import string
from datetime import datetime

import xlwt
from django.contrib import admin
from django.db.models import QuerySet, Sum
from django.db.models.functions import ExtractDay
from django.http import StreamingHttpResponse
from import_export.admin import ImportExportModelAdmin, ImportExportActionModelAdmin
from import_export import resources
from xlwt import Worksheet

from .models import GoodsCategory, Goods, Customer, StorageRecord, StorageRecordOfGoods, TicketSetting

admin.site.site_header = "后台管理系统"
admin.site.site_title = "后台管理系统"
admin.site.index_title = "后台管理系统"


class GoodsResource(resources.ModelResource):

    class Meta:
        model = Goods
        skip_unchanged = True
        fields = ('name', 'category__name', 'cost', 'selling_price', 'quantity_in_store', 'specification',
                  'unit')
        exclude = ('add_time', 'id')  # 排除
        export_order = ('category__name',)


@admin.register(Goods)
class GoodsAdmin(admin.ModelAdmin):
    list_display = ['name', 'category', 'cost', 'selling_price', 'quantity_in_store', 'specification',
                    'unit', 'add_time']
    list_filter = ['name', 'category', 'quantity_in_store', 'add_time']
    list_editable = ["category", "selling_price"]
    resource_class = GoodsResource

    # # 自定义导出excel表格
    # actions = ["save_execl"]
    # # date_hierarchy = "day_time" # 按日期显示
    #
    # def save_execl(self, request, queryset):
    #     table = xlwt.Workbook(encoding = 'utf-8')
    #     sheet = table.add_sheet("response")
    #     rows = 0
    #     for query in queryset:
    #         sheet.write(rows, 0, query.name)
    #         sheet.write(rows, 1, query.category.name)
    #         sheet.write(rows, 2, str(query.selling_price))
    #         sheet.write(rows, 3, str(query.quantity_in_store))
    #         sheet.write(rows, 4, query.specification)
    #         sheet.write(rows, 5, query.unit)
    #         rows += 1
    #
    #     file_name = "来货明细.xls"
    #     table.save(file_name)
    #
    #     def file_iterator(filename, chuck_size=512):
    #         with open(filename, "rb") as f:
    #             while True:
    #                 c = f.read(chuck_size)
    #                 if c:
    #                     yield c
    #                 else:
    #                     break
    #
    #     response = StreamingHttpResponse(file_iterator(file_name))
    #     response['Content-Type'] = 'application/octet-stream'
    #     date = datetime.now().strftime('%Y-%m')
    #     response['Content-Disposition'] = 'attachment;filename="{}.xls"'.format(date)
    #     return response
    #
    # save_execl.short_description = "导出Excel"  # 按钮显示名字


# class GoodsCategoryResource(resources.ModelResource):
#     class Meta:
#         model = GoodsCategory
#         skip_unchanged = True
#
#
# @admin.register(GoodsCategory)
# class GoodsCategoryAdmin(ImportExportActionModelAdmin):
#     resource_class = GoodsResource


admin.site.register(GoodsCategory)
admin.site.register(Customer)
# admin.site.register(Member)


class MapXlwt:
    def __init__(self, table, head):

        self.table = table
        self.head = dict(zip(["品名", "规格", "单位"] + head + ["数量", "进价", "金额"], range(len(head)+6)))

        div, mod =  divmod(len(head)+2, 24)
        self.head_end_str = string.ascii_uppercase[mod]
        if div:
            self.head_end_str = "A"+self.head_end_str

        div, mod =  divmod(len(self.head)-3, 24)
        self.amount_str = string.ascii_uppercase[mod]
        if div:
            self.amount_str = "A"+self.amount_str

        div, mod =  divmod(len(self.head)-2, 24)
        self.selling_price_str = string.ascii_uppercase[mod]
        if div:
            self.selling_price_str = "A"+self.selling_price_str

        self.sheet = {}

    def get_good(self, good: dict, sheet_good: dict, sheet: Worksheet):
        """
        返回商品所在的行索引，如果没有找到就新建个并返回行号
        :param good: 商品id等所在的字典
        :param sheet_good: 存储商品信息所在的字典
        :param sheet；sheet对象
        :return: 商品id所在的行号
        """
        try:
            line_number = sheet_good[good["goods"]]
        except KeyError:
            if len(sheet_good) == 0:
                # 从第一行开始
                line_number = 1
            else:
                line_number = sorted(sheet_good.values(), reverse=True)[0]+1
            # 一个月最多31天从空字符串到A，大小写都可以
            # sheet.write 行号是从0开始，但是公式是从1开始
            sheet.write(line_number, 0, good["goods__name"])
            sheet.write(line_number, 1, good["goods__specification"])
            sheet.write(line_number, 2, good["goods__unit"])
            sheet.write(line_number, len(self.head)-3, xlwt.Formula("SUM(D"+str(line_number+1)+":"+self.head_end_str+str(line_number+1)+")"))
            sheet.write(line_number, len(self.head)-2, good["goods__selling_price"])
            sheet.write(line_number, len(self.head)-1, xlwt.Formula(self.amount_str+str(line_number+1)+"*"
                                                                    +self.selling_price_str+str(line_number+1)))
            sheet_good[good["goods"]] = line_number
        return line_number

    def get_sheet(self, sheet_name: str):
        """
        返回表明所在的表对象,没有就新建一个并添加表头
        :param sheet_name:
        :return: 表对象
        """
        try:
            sheet = self.sheet[sheet_name]
        except KeyError:
            sheet_add_head = self.table.add_sheet(sheet_name, cell_overwrite_ok=True)
            for name, index in self.head.items():
                sheet_add_head.write(0, index, name)
            sheet = [sheet_add_head, {}]
            self.sheet[sheet_name] = sheet
        return sheet


@admin.register(StorageRecord)
class StorageRecordAdmin(admin.ModelAdmin):
    list_display = ['number', 'organization', 'total_pries', 'create_time']
    list_filter = ['number', 'organization', 'create_time']
    list_editable = ['create_time']

    # 自定义导出excel表格
    actions = ["save_execl"]

    # date_hierarchy = "day_time" # 按日期显示

    def save_execl(self, request, queryset):
        date_list_query = list(queryset.annotate(date=ExtractDay("create_time")).order_by("date").values("date").distinct())
        date_list = map(lambda x: str(x) + "号", [i["date"] for i in date_list_query])
        table = xlwt.Workbook(encoding='utf-8')
        # 存到哪一行，和哪一个表的映射
        maping = MapXlwt(table, list(date_list))

        for i in date_list_query:
            that_day_record = queryset.filter(create_time__day=i["date"])
            items = StorageRecordOfGoods.objects.filter(storage_record__in=that_day_record).values("goods")\
                .annotate(amounts=Sum("amount")).values("goods__category__name", "amounts", "goods", "goods__name",
                                                        "goods__selling_price", "goods__unit", "goods__specification")
            for item in items:
                sheet, sheet_good_dict = maping.get_sheet(item["goods__category__name"])
                sheet.write(maping.get_good(item, sheet_good_dict, sheet), maping.head[str(i["date"])+"号"], item["amounts"])

        file_name = "来货明细.xls"
        table.save(file_name)

        def file_iterator(filename, chuck_size=512):
            with open(filename, "rb") as f:
                while True:
                    c = f.read(chuck_size)
                    if c:
                        yield c
                    else:
                        break

        response = StreamingHttpResponse(file_iterator(file_name))
        response['Content-Type'] = 'application/octet-stream'
        date = datetime.now().strftime('%Y-%m')
        response['Content-Disposition'] = 'attachment;filename="{}.xls"'.format(date)
        return response

    save_execl.short_description = "导出Excel"  # 按钮显示名字


admin.site.register(StorageRecordOfGoods)


@admin.register(TicketSetting)
class TicketSettingAdmin(admin.ModelAdmin):
    def has_add_permission(self, request):
        return False

    def has_delete_permission(self, request, obj=None):
        return False
